﻿Imports System.Text
Public Class Cases
    Public Structure CaseInfo
        Public CaseId As Long
        Public ProductTypeId As Integer
        Public Productid As Integer
        Public SalesConid As Integer
        Public CaseWorkerId As Integer
        Public CaseCoordinatorID As Integer
        Public CandidateId As Long
        Public Contactid As Long
        Public ExtRef As String
        Public CriticalDate As Date
        Public CriticalType As String
        Public CompanyId As Long
        Public CaseStatusId As Integer
        Public OnHold As Boolean  'ActiveStatus
        Public ExpiryDate As Date
        Public PrimaryCaseId As Long
        Public EnteredBy As String
        Public SoldDate As Date
        Public SubmittedDate As Date
        Public PoliceMedsDate As Date
        Public EntryDate As Date
        Public CompanyName As String
        Public CandidateName As String
        Public ProductName As String
        Public CaseWorkerName As String
        Public CaseCoordinatorName As String
        Public CaseStageTitle As String
        Public StatusChangeDate As Date
        Public FastTrackDate As Date
        Public LinkedCaseID As Long
        Public QuestionsAllowed As Long
        Public PackExpiry As Date
        Public IsDIY As Boolean
        Public WrittenOffDate As Date
        Public CaseAssignCSMDate As Date
    End Structure
    Public Structure CaseStage
        Public StageNumber As Integer
        Public StageTitle As String
        Public StageDateSet As Date
        Public ActiveStatus As Integer  ' Onhold =1
    End Structure
    Public Enum LockState
        Successful
        Unsuccessful
        Disabled
    End Enum

    Public Structure CaseStageLookupItem
        Public StageId As Integer
        Public StageNumber As Integer
        Public StageTitle As String
        Public DateColumn As String
        Public DateOverwite As Boolean
    End Structure
    Public Shared CurrentLockedCaseID As Integer
    Public Shared Function CaseStageList() As DataTable
        Dim DB As New TestData.DataAccessSoapClient
        Return DB.GetSQLDataTable("SELECT CaseStage_Number, CaseStage_Title FROM L_CaseStages ORDER BY CaseStage_Number")
    End Function
    Public Shared Function GetCaseInfo(ByVal CaseId As Long) As Cases.CaseInfo
        Dim DB As New TestData.DataAccessSoapClient
        Try


            Dim SQL As New StringBuilder
            Dim myTable As DataTable

            GetCaseInfo = Nothing

            With SQL
                .Append("SELECT D_Case.Case_DTStatus, D_Case.Case_CoordinatorID, D_Case.Case_StatusID, isnull(D_Company.Company_Name,'Private') as Company, D_Person.Person_FName + ' ' + D_Person.Person_LName as Candidate, L_Products.Product_Name, L_Consultant.con_name as caseworkername, Coordinator.con_name as CoordinatorName, CaseStage_Title,")
                .Append("product_type,case_concwid,case_contactid,case_dtcritical,case_criticaltype,")
                .Append("case_extref,product_id,product_type,case_consaleid,case_companyid,case_candidateid,")
                .Append("Case_ActiveStatus,case_dtexpiry,case_dtsubmitted,case_dtpolicemeds,case_dtentry,case_DTFastTrack,Case_MainCaseID,Case_QuestionsAllowed,Case_DIYCase,Case_DTPackExpiry,Case_DIYCASE, D_FastTracks.FastTrack_DT as FastTrackDate, dbo.D_Case.Case_DTWrittenOff as WriteOffDate ")
                .Append(" FROM D_Case LEFT OUTER JOIN")
                .Append(" L_Consultant ON D_Case.Case_ConCWID = L_Consultant.Con_ID LEFT OUTER JOIN")
                .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
                .Append(" D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN")
                .Append(" D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID LEFT OUTER JOIN")
                .Append(" L_Consultant as Coordinator on D_Case.Case_CoordinatorID = Coordinator.Con_ID LEFT OUTER JOIN")
                .Append(" L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number Left join ")
                .Append(" D_FastTracks on D_Case.Case_FastTrackID = D_FastTracks.FastTrack_ID ")
                .Append(" WHERE D_Case.Case_ID = " & CaseId)
                myTable = DB.GetSQLDataTable(.ToString)

                Try

                    If Not IsNothing(myTable) Then
                        If myTable.Rows.Count > 0 Then

                            Dim myRow As DataRow = myTable.Rows(0)
                            Dim myCase As New Cases.CaseInfo

                            With myCase
                                .CaseId = CaseId
                                .CaseWorkerId = myRow.Item("case_concwid")
                                .Contactid = myRow.Item("case_contactid")
                                .CriticalDate = IIf(IsDBNull(myRow.Item("case_dtcritical")), Nothing, myRow.Item("case_dtcritical"))
                                .CriticalType = IIf(IsDBNull(myRow.Item("case_criticaltype")), "", myRow.Item("case_criticaltype"))
                                .ExtRef = IIf(IsDBNull(myRow.Item("case_extref")), "", myRow.Item("case_extref"))
                                If myRow.Item("product_id").ToString <> "" Then
                                    .Productid = myRow.Item("product_id")
                                    '.Productid = CInt(myRow.Item("product_id"))
                                End If
                                If myRow.Item("product_type").ToString <> "" Then
                                    .ProductTypeId = myRow.Item("product_type")
                                End If
                                .SalesConid = myRow.Item("case_consaleid")
                                .CaseCoordinatorID = IIf(IsDBNull(myRow.Item("case_CoordinatorID")), 0, myRow.Item("case_CoordinatorID"))
                                '.CaseCoordinatorID = myRow.Item("case_CoordinatorID")
                                .CompanyId = myRow.Item("case_companyid")
                                .CaseStatusId = myRow.Item("Case_statusID")
                                .CandidateId = myRow.Item("case_candidateid")
                                .OnHold = IIf(myRow.Item("Case_ActiveStatus") = 1, True, False)
                                .ExpiryDate = IIf(IsDBNull(myRow.Item("case_dtexpiry")), Nothing, myRow.Item("case_dtexpiry"))
                                .SubmittedDate = IIf(IsDBNull(myRow.Item("case_dtsubmitted")), Nothing, myRow.Item("case_dtsubmitted"))
                                .PoliceMedsDate = IIf(IsDBNull(myRow.Item("case_dtpolicemeds")), Nothing, myRow.Item("case_dtpolicemeds"))
                                .EntryDate = IIf(IsDBNull(myRow.Item("case_dtentry")), Nothing, myRow.Item("case_dtentry"))
                                .CompanyName = IIf(IsDBNull(myRow.Item("company")), Nothing, myRow.Item("company"))
                                .CandidateName = IIf(IsDBNull(myRow.Item("candidate")), Nothing, myRow.Item("candidate"))
                                .ProductName = IIf(IsDBNull(myRow.Item("product_name")), Nothing, myRow.Item("product_name"))
                                .CaseWorkerName = IIf(IsDBNull(myRow.Item("caseworkername")), Nothing, myRow.Item("caseworkername"))
                                .CaseCoordinatorName = IIf(IsDBNull(myRow.Item("CoordinatorName")), Nothing, myRow.Item("CoordinatorName"))
                                .CaseStageTitle = IIf(IsDBNull(myRow.Item("casestage_title")), Nothing, myRow.Item("casestage_title"))
                                .StatusChangeDate = IIf(IsDBNull(myRow.Item("case_dtstatus")), Nothing, myRow.Item("case_dtstatus"))
                                .FastTrackDate = IIf(IsDBNull(myRow.Item("FastTrackDate")), Nothing, myRow.Item("FastTrackDate"))
                                .LinkedCaseID = IIf(IsDBNull(myRow.Item("Case_MainCaseID")), 0, myRow.Item("case_MainCaseID"))
                                .QuestionsAllowed = IIf(IsDBNull(myRow.Item("Case_QuestionsAllowed")), 0, myRow.Item("case_QuestionsAllowed"))
                                .PackExpiry = IIf(IsDBNull(myRow.Item("case_dtPackExpiry")), Nothing, myRow.Item("case_dtPackExpiry"))
                                .IsDIY = IIf(myRow.Item("case_DIYCase").GetType.ToString = "System.DBNull", False, IIf(myRow.Item("case_DIYCase").ToString = "1", True, False))
                                .WrittenOffDate = IIf(IsDBNull(myRow.Item("WriteOffDate")), Nothing, myRow.Item("WriteOffDate"))

                            End With

                            Return myCase

                        End If
                    End If

                Catch ex As System.Exception

                    'MsgBox(ex.Message, MsgBoxStyle.Critical, "Case Information")
                    Return Nothing
                Finally
                    myTable.Dispose()
                End Try

            End With
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    Public Shared Function GetCaseIdFromInvoiceID(ByVal InvoiceId As Long) As Long
        Dim db As New TestData.DataAccessSoapClient
        Try


            ' get Case_ID from Invoice_ID
            ' you may need to expand this to get Case_ID from sub ID values later
            Return db.GetSQLScalar("Select Invoice_CaseID From A_Invoices Where Invoice_ID = " & InvoiceId)
        Catch ex As Exception
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function GetCaseStage(ByVal CaseID As Long) As CaseStage
        Dim DB As New TestData.DataAccessSoapClient
        Dim SQL As New StringBuilder
        Dim myData As DataTable

        GetCaseStage = Nothing

        If CaseID > 0 Then

            With SQL
                .Append("select st.casestage_title, c.case_statusid, c.case_dtstatus,c.case_activestatus ")
                .Append("from l_casestages st, d_case c ")
                .Append("where c.case_statusid = st.casestage_number ")
                .Append("and c.case_id = " & CaseID)
                myData = DB.GetSQLDataTable(.ToString)
            End With

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then

                    Dim myStage As CaseStage

                    With myStage
                        .StageDateSet = myData.Rows(0).Item("case_dtstatus")
                        .StageTitle = myData.Rows(0).Item("casestage_title")
                        .StageNumber = myData.Rows(0).Item("case_statusid")
                        .ActiveStatus = myData.Rows(0).Item("case_activestatus")
                    End With

                    Return myStage

                End If
            End If
        End If


    End Function
    Public Shared Function LockCase(ByVal lngCaseID As Integer, ByVal lngUserID As Long) As LockState

        Dim sQL As New StringBuilder
        Dim objDT As DataTable
        Dim objDR As DataRow
        Dim DB As New TestData.DataAccessSoapClient

        Try

            If CommonDataFunctions.GetOption("LockingEnabled") = "N" Then
                Return LockState.Disabled
                CurrentLockedCaseID = 0
                Exit Function
            End If

            DB.ExecuteSQL("Update D_Case set Case_Locked=" & CStr(lngUserID) & " Where Case_Locked = 0 and Case_ID = " & CStr(lngCaseID))


            With sQL
                .Append("SELECT dbo.L_Consultant.con_name, dbo.D_Case.Case_ID, dbo.D_Case.Case_Locked ")
                .Append("FROM dbo.D_Case LEFT OUTER JOIN ")
                .Append("dbo.L_Consultant ON dbo.D_Case.Case_Locked = dbo.L_Consultant.Con_ID ")
                .Append("Where Case_ID = " & CStr(lngCaseID))
            End With

            objDT = DB.GetSQLDataTable(sQL.ToString)

            If objDT Is Nothing Then
                'CommonFuncs.DisplayMessage("The case could not be locked, please inform IT with case number.", CommonDialog.CustomDialogIcons.Information, "Error")
                CurrentLockedCaseID = 0
                Return LockState.Unsuccessful
            Else
                If objDT.Rows.Count = 0 Then
                    'CommonFuncs.DisplayMessage("The case could not be locked, please inform IT with case number.", CommonDialog.CustomDialogIcons.Information, "Error")
                    CurrentLockedCaseID = 0
                    Return LockState.Unsuccessful
                End If
            End If

            objDR = objDT.Rows(0)

            If objDR("Case_Locked") <> lngUserID Then
                ''CommonFuncs.DisplayMessage("The case is locked by " & objDR("con_name"), CommonDialog.CustomDialogIcons.Information, "Error")
                CurrentLockedCaseID = 0
                Return LockState.Unsuccessful
            Else
                CurrentLockedCaseID = lngCaseID
                Return LockState.Successful
            End If

        Catch ex As Exception
            'CommonFuncs.ErrorHandler(ex, "Cases.LockCase", False)
        Finally
            objDT = Nothing
            objDR = Nothing
        End Try
    End Function
    Public Shared Function UnLockCase(ByVal lngCaseID As Long, ByVal lngUserID As Long) As LockState
        Dim sQL As New StringBuilder
        Dim objDT As DataTable
        Dim objDR As DataRow
        Dim DB As New TestData.DataAccessSoapClient

        Try

            If CommonDataFunctions.GetOption("LockingEnabled") = "N" Then
                Return LockState.Disabled
                Exit Function
            End If

            DB.ExecuteSQL("Update D_Case set Case_Locked=0 Where Case_Locked = " & CStr(lngUserID) & " and Case_ID = " & CStr(lngCaseID))


            With sQL
                .Append("SELECT dbo.L_Consultant.con_name, dbo.D_Case.Case_ID, dbo.D_Case.Case_Locked ")
                .Append("FROM dbo.D_Case LEFT OUTER JOIN ")
                .Append("dbo.L_Consultant ON dbo.D_Case.Case_Locked = dbo.L_Consultant.Con_ID ")
                .Append("Where Case_ID = " & CStr(lngCaseID))
            End With

            objDT = DB.GetSQLDataTable(sQL.ToString)

            If objDT Is Nothing Then
                'CommonFuncs.DisplayMessage("The case could not be locked, please inform IT with case number.", CommonDialog.CustomDialogIcons.Information, "Error")
                Return LockState.Unsuccessful
            Else
                If objDT.Rows.Count = 0 Then
                    'CommonFuncs.DisplayMessage("The case could not be locked, please inform IT with case number.", CommonDialog.CustomDialogIcons.Information, "Error")
                    Return LockState.Unsuccessful
                End If
            End If

            objDR = objDT.Rows(0)

            If objDR("Case_Locked") <> 0 Then
                'CommonFuncs.DisplayMessage("The case is locked by " & objDR("con_name"), CommonDialog.CustomDialogIcons.Information, "Error")
                Return LockState.Unsuccessful
            Else
                CurrentLockedCaseID = 0
                Return LockState.Successful
            End If

        Catch ex As Exception
            'CommonFuncs.ErrorHandler(ex, "Cases.LockCase", False)
        Finally
            objDT = Nothing
            objDR = Nothing
        End Try
    End Function
    Public Shared Function GetCaseList(ByVal PersonId As Long) As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New TestData.DataAccessSoapClient
        With SQL
            .Append("select c.case_id, convert(varchar(10),c.case_id) + '  |  ' + p.product_name + '  |  ' + s.casestage_title as displayname ")
            .Append(" from d_case c, l_products p, l_casestages s")
            .Append(" where c.case_candidateid = " & PersonId)
            .Append(" and c.case_productid = p.product_id")
            .Append(" and c.case_statusid = s.casestage_number")
        End With

        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetCaseEvents(ByVal CaseId As Long, ByVal PersonId As Long) As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New TestData.DataAccessSoapClient
        With SQL
            .Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT,")
            .Append("D_Event.Event_Description, L_Consultant.con_name, CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),")
            .Append("Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category,")
            .Append("Case When Event_LinkType = 1 then 'Case'  when Event_LinkType = 2 then 'Client' end as LinkFor")
            .Append(" FROM D_Event LEFT OUTER JOIN L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")
            .Append(" WHERE (D_Event.Event_LinkType = 1) And (D_Event.Event_LinkID = " & CaseId & ")")
            If PersonId > 0 Then
                .Append(" OR (D_Event.Event_LinkType = 2) And (D_Event.Event_LinkID = " & PersonId & ")")
            End If
            .Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc ")

        End With

        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetKeyDates(ByVal CaseId As Long) As DataTable

        Dim DiagId As Integer
        Dim SQL As New StringBuilder
        Dim myDiag As DataTable
        Dim DB As New TestData.DataAccessSoapClient
        DiagId = CommonDataFunctions.GblFunc_Get_Dataset(CaseId, 1) 'gets CSD from product or product type using flag 1 (cas stage dia)

        With SQL
            .Append("SELECT MIN(L_CaseStages.CaseStage_Title) AS DateTitle, L_CaseStages.CaseStage_DTColumn AS DateColumn")
            .Append(" FROM L_CaseStages INNER JOIN L_StageLinks ON L_CaseStages.CaseStage_Number = L_StageLinks.StageLink_ToID")
            .Append(" WHERE (L_StageLinks.StageLink_DiagramID = " & DiagId & ") AND (L_CaseStages.CaseStage_DTColumn <> '')")
            .Append(" GROUP BY L_CaseStages.CaseStage_DTColumn ORDER BY MIN(L_CaseStages.CaseStage_Number)")
        End With

        myDiag = DB.GetSQLDataTable(SQL.ToString)

        If myDiag.Rows.Count > 0 Then

            Dim myRow As DataRow
            Dim KeyDates As New DataTable 'StringBuilder
            Dim PrevDate As Date = "01-Jan-1901"
            Dim myDates As DataTable
            Dim myKeyDate As DataRow
            Dim i As Integer

            With KeyDates.Columns
                .Add(New DataColumn("stagename", System.Type.GetType("System.String")))
                .Add(New DataColumn("stagedate", System.Type.GetType("System.DateTime")))
                .Add(New DataColumn("workingdays", System.Type.GetType("System.Int16")))
            End With

            SQL = New StringBuilder

            With SQL
                .Append("SELECT Case_ID, Case_DTSold, Case_DTCWRev, Case_DTSkillsSub, Case_DTSkillsRet, Case_DTSecondSub, Case_DTSecondRet, Case_DTSubmitted, ")
                .Append(" Case_DTChecks , Case_DTGranted, Case_DTClosed")
                .Append(" FROM D_Case WHERE Case_ID = " & CaseId)
            End With

            myDates = DB.GetSQLDataTable(SQL.ToString)

            For Each myRow In myDiag.Rows

                myKeyDate = KeyDates.NewRow

                With myKeyDate
                    .Item("stagename") = myRow.Item("datetitle")
                    .Item("stagedate") = myDates.Rows(0).Item(myRow.Item("DateColumn"))
                    If PrevDate > "2-Jan-1901" And Not IsDBNull(myDates.Rows(0).Item(myRow.Item("DateColumn"))) Then
                        i = CommonFuncs.DateDiffW((New CommonFuncs()).FormatDate(PrevDate), myDates.Rows(0).Item(myRow.Item("DateColumn")))
                        If i > 0 Then
                            .Item("workingdays") = i
                        End If
                    End If
                End With





                'If KeyDates.ToString <> "" Then
                '    .Append(";")
                'End If

                '.Append(myRow.Item("datetitle") & "; ")
                '.Append(myDates.Rows(0).Item(myRow.Item("DateColumn")) & ";")
                'If PrevDate > "2-Jan-1901" And Not IsDBNull(myDates.Rows(0).Item(myRow.Item("DateColumn"))) Then
                '    .Append(DateDiffW(FormatDate(PrevDate), myDates.Rows(0).Item(myRow.Item("DateColumn"))))
                '    'benchmarking would sit in her somewhere

                'Else
                '    'Do nothing
                'End If



                If Not IsDBNull(myDates.Rows(0).Item(myRow.Item("DateColumn"))) Then
                    PrevDate = myDates.Rows(0).Item(myRow.Item("DateColumn"))
                End If


                KeyDates.Rows.Add(myKeyDate)

            Next

            Return KeyDates
        Else
            Return Nothing
        End If



    End Function

    Public Shared Function GetCaseId(ByVal InvoiceId As Long) As Long
        ' get Case_ID from Invoice_ID
        ' you may need to expand this to get Case_ID from sub ID values later
        Dim DB As New TestData.DataAccessSoapClient
        Return DB.GetSQLScalar("Select Invoice_CaseID From A_Invoices Where Invoice_ID = " & InvoiceId)

    End Function
    Public Shared Function GetCaseStageLookup(ByVal StageNo As Integer) As CaseStageLookupItem
        Dim DB As New TestData.DataAccessSoapClient
        Dim myData As DataTable
        Try


            GetCaseStageLookup = Nothing

            myData = DB.GetSQLDataTable("select casestage_id,casestage_number,casestage_title,casestage_dtcolumn,casestage_dtoverwrite from L_CaseStages where CaseStage_Number = " & StageNo)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    Dim myStage As New CaseStageLookupItem

                    With myStage
                        .StageId = myData.Rows(0).Item("casestage_id")
                        .StageNumber = myData.Rows(0).Item("casestage_number")
                        .StageTitle = IIf(IsDBNull(myData.Rows(0).Item("casestage_title")), "", myData.Rows(0).Item("casestage_title"))
                        .DateColumn = IIf(IsDBNull(myData.Rows(0).Item("casestage_dtcolumn")), "", myData.Rows(0).Item("casestage_dtcolumn"))
                        .DateOverwite = IIf(myData.Rows(0).Item("casestage_dtoverwrite") = 1, True, False)
                    End With

                    myData.Dispose()

                    Return myStage
                End If
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            myData = Nothing
            DB = Nothing
        End Try
    End Function
    Public Shared Function CreateNewCase(ByVal CaseInfo As CaseInfo) As Long
        Dim DB As New TestData.DataAccessSoapClient
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("Insert Into D_Case (Case_EnteredBy, Case_ProductID, Case_ConSaleID, Case_ConCWID")
                .Append(" , Case_CompanyID, Case_StatusID, Case_DTStatus, Case_DTsold, Case_CandidateID, Case_ContactID, Case_PrimaryCaseID, Case_CoordinatorID)")
                .Append(" VALUES(")
                .Append(" '" & cmn.SwapQuotes(CaseInfo.EnteredBy) & "',")
                .Append(CaseInfo.Productid & ",")
                .Append(CaseInfo.SalesConid & ",")
                .Append(CaseInfo.CaseWorkerId & ",")
                .Append(CaseInfo.CompanyId & ",")
                .Append(CaseInfo.CaseStatusId & ",")
                .Append(" getdate() ,")
                If IsDate(CaseInfo.SoldDate) Then
                    .Append("'" & cmn.FormatDate(CaseInfo.SoldDate, True) & "',")
                Else
                    .Append("null,")
                End If
                .Append(CaseInfo.CandidateId & ",")
                .Append(CaseInfo.Contactid & ", ")
                .Append(CaseInfo.PrimaryCaseId & ",")
                .Append(CaseInfo.CaseCoordinatorID & ")")
            End With

            Return DB.ExecuteSQL1(SQL.ToString, True)


        Catch ex As Exception
            Return 0
        Finally
            cmn = Nothing
            DB = Nothing
        End Try
    End Function

    Public Shared Function CreateNewCaseDocReqs(ByVal CaseId As Long, ByVal ConName As String, ByVal ProductId As Integer) As Boolean

        Dim DB As New TestData.DataAccessSoapClient
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("INSERT INTO D_DocReqs")
                .Append(" (DocReq_Name, DocReq_Type, DocReq_EnteredBy, DocReq_CaseID)")
                .Append(" SELECT DefDocReq_Name, DefDocReq_Type, '" & cmn.SwapQuotes(ConName.Trim) & "' AS EB, " & CaseId & " AS CID")
                .Append(" FROM D_DefDocReqs")
                .Append(" WHERE (DefDocReq_ProductID = " & ProductId & ")")
            End With

            Return DB.ExecuteSQL(SQL.ToString)

        Catch ex As Exception
            Return 0
        Finally
            SQL = Nothing
            DB = Nothing
            cmn = Nothing
        End Try

    End Function
    Public Shared Function AddCFF(ByVal CaseId As Long, ByVal StageLinkTo As Integer, ByVal Reason As String, ByVal ConName As String) As Long


        Dim DB As New TestData.DataAccessSoapClient
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("Insert into D_CFF (CFF_EnteredBy, CFF_DTEntered, CFF_Type, CFF_CaseID, CFF_Details)")
                .Append(" VALUES('" & cmn.SwapQuotes(ConName.Trim) & "', '" & cmn.FormatDate(Now, True) & "',")
                If StageLinkTo = 1010 Then
                    .Append("2,")
                Else
                    .Append("1,")
                End If
                .Append(CaseId & ", '" & cmn.SwapQuotes(Reason.Trim) & "')")
            End With

            Return DB.ExecuteSQL(SQL.ToString)

        Catch ex As Exception
            Return 0
        Finally
            SQL = Nothing
            DB = Nothing
        End Try

    End Function
    Public Shared Function UpdateCase(ByVal CaseInfo As CaseInfo) As Boolean
        Dim DB As New TestData.DataAccessSoapClient
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("update d_case set case_productid = " & CaseInfo.Productid & ",")
                .Append("case_consaleid = " & CaseInfo.SalesConid & ",")
                .Append("case_concwid = " & CaseInfo.CaseWorkerId & ",")
                .Append("case_candidateid = " & CaseInfo.CandidateId & ",")
                .Append("case_contactid = " & CaseInfo.Contactid & ",")
                .Append("case_CoordinatorID = " & CaseInfo.CaseCoordinatorID & ",")
                .Append("case_extref = '" & cmn.SwapQuotes(CaseInfo.ExtRef.Trim) & "',")
                .Append("case_dtexpiry = '" & cmn.FormatDate(CaseInfo.ExpiryDate) & "',")
                .Append("case_dtcritical = '" & cmn.FormatDate(CaseInfo.CriticalDate) & "',")
                .Append("case_criticaltype = '" & cmn.SwapQuotes(CaseInfo.CriticalType.Trim) & "',")
                .Append("case_dtpolicemeds = '" & cmn.FormatDate(CaseInfo.PoliceMedsDate) & "',")
                .Append("case_dtentry = '" & cmn.FormatDate(CaseInfo.EntryDate) & "', ")
                .Append("case_QuestionsAllowed = " & CaseInfo.QuestionsAllowed & " ")
                .Append("where case_id = " & CaseInfo.CaseId)

            End With

            Return CommonDataFunctions.ExecuteSQL(SQL.ToString)

        Catch ex As Exception
            Return False
        Finally
            SQL = Nothing
            cmn = Nothing
            DB = Nothing
        End Try
    End Function
    Public Shared Function ResetCaseDocs(ByVal CaseId As Long, ByVal ProductId As Integer, ByVal ConsultantName As String, Optional ByVal CandidateId As Long = 0, Optional ByVal CompanyId As Long = 0) As Boolean

        Dim DB As New TestData.DataAccessSoapClient
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs
        Try



            Dim FirstCase As Boolean
            Dim ClientType As Integer

            SQL = New StringBuilder
            'Clear document requirements
            With SQL
                .Append("Delete from D_DocReqs where DocReq_CaseId = " & CaseId & " AND DocReq_DTReceived IS NULL")
            End With

            If Not DB.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            'Check for previous initiated cases on this account
            SQL = New StringBuilder

            With SQL
                .Append("Select count(*) from D_Case Where Case_ID < " & CaseId)
                .Append(" And Case_StatusID Between 100 and 1020")

                If CompanyId = 0 Then
                    ClientType = 1
                    .Append(" AND case_CandidateId = " & CandidateId)
                Else
                    .Append(" AND case_CompanyId = " & CompanyId)
                End If
            End With

            If DB.GetSQLScalar(SQL.ToString) = 0 Then
                FirstCase = True
            End If

            SQL = New StringBuilder

            With SQL
                .Append("INSERT INTO D_DocReqs")
                .Append(" (DocReq_Name, DocReq_Type, DocReq_TypeID, DocReq_DTEntered, DocReq_EnteredBy, DocReq_CaseID)")
                .Append(" SELECT DefDocReq_Name, DefDocReq_Type, DefDocReq_TypeID ,GETDATE() AS Entered, '" & cmn.SwapQuotes(ConsultantName) & "' AS enteredBy, " & CaseId & " AS CaseID")
                .Append(" FROM D_DefDocReqs")
                .Append(" Left Outer Join L_DocReqTypes on D_DefDocReqs.DefDocReq_TypeID = DocReqType_ID")
                .Append(" WHERE (DefDocReq_ProductID = " & ProductId & ")")
                .Append(" AND (DocReqType_ClientType IN (0," & ClientType & "))")
                If Not FirstCase Then
                    .Append(" AND (DocReqType_FTO = 0)")
                End If
            End With

            If DB.ExecuteSQL(SQL.ToString) Then
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            SQL = Nothing
            DB = Nothing
            cmn = Nothing
        End Try
    End Function
    Public Shared Function UpdateDateAssigned(ByVal caseid As Long, ByVal assignedby As Long) As Boolean
        Dim DB As New TestData.DataAccessSoapClient
        Try


            If DB.ExecuteSQL("Update d_case set case_dtassigned = getdate(),case_assignedby =" & assignedby & " where case_id = " & caseid) = True Then
                Return True
            Else : Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function GetCaseDepartment(ByVal CaseId As Long) As Integer
        Dim DB As New TestData.DataAccessSoapClient
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select producttype_departmentid from d_case cas, l_products p, l_producttypes pt ")
                .Append("where cas.Case_ID = " & CaseId & " and cas.case_productid = p.product_id ")
                .Append("and p.product_type = pt.producttype_id")
            End With

            Return DB.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

End Class
